mysql操作语句大全 |
您所在的位置：网站首页 › vim 操作大全 › mysql操作语句大全 |
mysql操作语句大全
|
连接mysql详情见https://blog.csdn.net/csdjia11/article/details/12057818 输入：mysql -uroot -p123456 即可登录MySQL，如果出现command not found: mysql，就再执行一下 source /etc/profile 让设置立即生效后再尝试 数据库相关 # 查询数据库 SHOW DATABASES; # 创建数据库 CREATE DATABASE sqlCodeTestDb; # 删除数据库 drop database willDeleteDb; # 选择数据库 use sqlCodeTestDb;表相关 CREATE TABLE `item_order_0` ( `id` BIGINT(20) UNSIGNED NOT NULL COMMENT '主键', `user_id` BIGINT(20) UNSIGNED NOT NULL COMMENT '用户id', `shop_id` BIGINT(20) UNSIGNED NOT NULL COMMENT '店家id', `order_no` VARCHAR(50) NOT NULL COMMENT '幂等支付订单号', `order_amount` BIGINT(20) UNSIGNED NOT NULL COMMENT '订单金额（单位:分）', `status` TINYINT(3) NOT NULL DEFAULT 1 COMMENT '状态 1初始化 2支付失败 3支付成功 4已发货 5已签收', `item_id` VARCHAR(50) NOT NULL DEFAULT '0' COMMENT '商品id', `create_time` BIGINT(20) UNSIGNED NOT NULL COMMENT '创建时间', `update_time` BIGINT(20) UNSIGNED NOT NULL COMMENT '更新时间', `data` JSON COMMENT 'data 收货地址、快递单号等', PRIMARY KEY (`id`), UNIQUE KEY `uniq_idx_order_no`(`order_no`), KEY `idx_uid` (`user_id`), KEY `idx_status_time` (`status`,`update_time`), KEY `idx_uid_create_time` (`user_id`,`create_time`) ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT ='购买商品订单表'; # 列出所有表 SHOW FULL TABLES;插入数据 INSERT INTO `sqlcodetestdb`.`item_order_0` (`id`, `user_id`, `shop_id`, `order_no`, `order_amount`, `status`, `item_id`, `create_time`, `update_time`, `data`) VALUES (1, 31415926, 1, 'ORDER_31415926_1', 100, 1, 'SKU2022', 1675872000000, 1675872000000, '{\"recipient\": \"张三\", \"mobile\": \"15243322885\", \"address\": {\"province\": \"北京市\", \"city\": \"北京市\", \"district\": \"通州区\", \"town\": \"台湖镇\", \"address\": \"北小营路次渠嘉园\"}, \"payPlatform\": \"WEIXIN\", \"hasAddressInfo\": true}'); INSERT INTO `sqlcodetestdb`.`item_order_0` (`id`, `user_id`, `shop_id`, `order_no`, `order_amount`, `status`, `item_id`, `create_time`, `update_time`, `data`) VALUES (2, 31415926, 1, 'ORDER_31415926_2', 200, 2, 'SKU2023', 1675872000000, 1675872000000, '{\"recipient\": \"李四\", \"mobile\": \"15243322885\", \"address\": {\"province\": \"北京市\", \"city\": \"北京市\", \"district\": \"昌平区\", \"town\": \"延寿镇\", \"address\": \"黑山寨村\"}, \"payPlatform\": \"WEIXIN\", \"hasAddressInfo\": true}'); INSERT INTO `sqlcodetestdb`.`item_order_0` (`id`, `user_id`, `shop_id`, `order_no`, `order_amount`, `status`, `item_id`, `create_time`, `update_time`, `data`) VALUES (3, 31415926, 1, 'ORDER_31415926_3', 300, 3, 'SKU2024', 1675872000000, 1675872000000, '{\"recipient\": \"王五\", \"mobile\": \"15243322885\", \"address\": {\"province\": \"浙江省\", \"city\": \"杭州市\", \"district\": \"余杭区\", \"town\": \"余杭街道\", \"address\": \"金沙公寓小区\"}, \"payPlatform\": \"WEIXIN\", \"hasAddressInfo\": true}'); INSERT INTO `sqlcodetestdb`.`item_order_0` (`id`, `user_id`, `shop_id`, `order_no`, `order_amount`, `status`, `item_id`, `create_time`, `update_time`, `data`) VALUES (4, 31415926, 1, 'ORDER_31415926_4', 400, 4, 'SKU2025', 1675872000000, 1675872000000, '{\"recipient\": \"赵六\", \"mobile\": \"15243322885\", \"address\": {\"province\": \"陕西省\", \"city\": \"西安市\", \"district\": \"雁塔区\", \"town\": \"小寨路街道\", \"address\": \"雁塔西路76号\"}, \"payPlatform\": \"WEIXIN\", \"hasAddressInfo\": true}');查询 SELECT id, concat(data->>'$.address.province', data->>'$.address.city', data->>'$.address.district', data->>'$.address.town', data->>'$.address.address') AS fullAddress FROM item_order_0;
SELECT id, concat(data->'$.address.province',
data->'$.address.city',
data->'$.address.district',
data->'$.address.town',
data->'$.address.address') AS fullAddress FROM item_order_0;
更新 UPDATE item_order_0 SET data=json_replace(data,'$.address.province', '南京市') where id = 1; UPDATE item_order_0 SET data=json_set(data,'$.address.fullAddress', '南京市') where id = 1;
更多json操作见：https://www.cnblogs.com/feng-gamer/articles/10576190.html 根据表中某几个字段更新同一张表中另一个字段 UPDATE item_order_0 table1 INNER JOIN ( SELECT concat(data->>'$.address.province', data->>'$.address.city', data->>'$.address.district', data->>'$.address.town', data->>'$.address.address') AS fullAddress, id FROM item_order_0 WHERE data->>'$.hasAddressInfo' = 'true' ) table2 ON table1.id = table2.id SET table1.data = json_set(table1.data,'$.address.fullAddress', table2.fullAddress) WHERE data->>'$.hasAddressInfo' = 'true' |
【本文地址】